﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using FJERP.Model;

namespace FJERP.DAL
{
    public class SellStatisticsDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 根据时间范围查询零售主表
        /// </summary>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public List<Shipments> SearchShipmentsBydate(string DateStart, string DateEnd)
        {
            string sql = "select Shipments_Id,Shipments_blitems,Shipments_data,Employee_Name,MemberCard_Name,WareHose_Name,Shipments_Price from Shipments s join Employee e on s.Employee_Id=e.Employee_Id join MemberCard m on s.MemberCard_Id=m.MemberCard_Id join WareHose w on s.WareHose_Id=w.WareHose_Id WHERE Shipments_data BETWEEN CONVERT(datetime,'" + DateStart + "') AND CONVERT(datetime,'" + DateEnd + "') and Shipments_State=1";
            List<Shipments> list = new List<Shipments>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Shipments s = new Shipments();
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Shipments_blitems = item["Shipments_blitems"].ToString();
                s.Shipments_data = Convert.ToDateTime(item["Shipments_data"]);
                s.Employee_Name = item["Employee_Name"].ToString();
                s.MemberCard_Name = item["MemberCard_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Shipments_Price = Convert.ToDecimal(item["Shipments_Price"]);

                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 总盈利金额
        /// </summary>
        /// <param name="DateStart"></param>
        /// <param name="DateEnd"></param>
        /// <returns></returns>
        public int SumMoney(string DateStart, string DateEnd)
        {
            string sql = "select sum((Product_Price-Product_Bprid)*ShipmentMate_Num) from Product p join ShipmentMate s on p.Product_Id=s.Product_Id join Shipments ss on s.Shipments_Id=ss.Shipments_Id where Shipments_data BETWEEN CONVERT(datetime,'" + DateStart + "') AND CONVERT(datetime,'" + DateEnd + "') and Shipments_State=1";
            if (h.ExecScalar(sql) == System.DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32(h.ExecScalar(sql));
        }
        public int OneSumMoney(int id)
        {
            string sql = "select sum((Product_Price-Product_Bprid)*ShipmentMate_Num) from Product p join ShipmentMate s on p.Product_Id=s.Product_Id where s.Shipments_Id=" + id + "";
            if (h.ExecScalar(sql) == System.DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32(h.ExecScalar(sql));
        }
    }
}
